package com.lovo.manage_back.util;

import com.lovo.manage_back.bean.Suppliers;
import com.lovo.manage_back.result.GlobalHandleException;
import com.lovo.manage_back.result.ResultCode;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {
    public static List readExcel(String fileName) throws GlobalHandleException {
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
        Workbook workbook = null;
        try {
            FileInputStream fis = new FileInputStream(fileName);
            if(suffix.equalsIgnoreCase("xls")){
                workbook = new HSSFWorkbook(fis);
            }else if(suffix.equalsIgnoreCase("xlsx")){
                workbook = new XSSFWorkbook(fis);
            }
        } catch (FileNotFoundException e) {
            throw new GlobalHandleException(ResultCode.EXECEL_FORMAT_ERROR);
        } catch (IOException e) {
            throw new GlobalHandleException(ResultCode.EXECEL_FORMAT_ERROR);
        }

        Sheet sheet = workbook.getSheetAt(0);

        if(sheet == null){
            throw new GlobalHandleException(ResultCode.EXECEL_FORMAT_ERROR);
        }

        // 获取第一行数据（第一行是标头，必须存在，否则抛出格式错误的异常）
        Row firstRow = sheet.getRow(sheet.getFirstRowNum());
        if(firstRow == null){
            throw new GlobalHandleException(ResultCode.EXECEL_FORMAT_ERROR);
        }
        ArrayList list = new ArrayList();
        for(int i = 1;i < sheet.getPhysicalNumberOfRows();i++){
            Row row = sheet.getRow(i);
            if(row == null){
                continue;
            }
            Suppliers suppliers = new Suppliers();
            suppliers.setName(row.getCell(0).getStringCellValue());
            suppliers.setPerson(row.getCell(1).getStringCellValue());
            if(row.getCell(2).getCellType() == CellType.NUMERIC){
                DecimalFormat df = new DecimalFormat("0");
                suppliers.setPersonPhone(df.format(row.getCell(2).getNumericCellValue()));
            }else{
                suppliers.setPersonPhone(row.getCell(2).getStringCellValue());
            }
            suppliers.setAddr(row.getCell(3).getStringCellValue());
            suppliers.setBusiness(row.getCell(4).getStringCellValue());
            list.add(suppliers);
        }
        return list;
    }

}
